﻿
Partial Class WebForm_ImportData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack() Then
            '判斷是否有找到()
            If DirectCast(Master.FindControl("lblHeadText"), Label) IsNot Nothing Then
                DirectCast(Master.FindControl("lblHeadText"), Label).Text = Me.GetLocalResourceObject("Page.Text")
            End If
            Me.BindDBColumnName()
            Me.BindFileColumnName()
            Me.divMatch.Visible = False
            Me.BindGV()
        End If
    End Sub

    Protected Sub btnSent_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSent.Click
        For i As Integer = 0 To Me.gvMatch.Rows.Count - 1
            Dim ddlFileColumnName As DropDownList = TryCast(Me.gvMatch.Rows(i).FindControl("ddlFileColumnName"), DropDownList)
            If ddlFileColumnName.SelectedValue = "" Then
                MyObj.ShowMessage("請確定所有欄位皆已完成對應!!")
                Return
            End If
        Next

        Dim colProductName, colMonth, colForeCastCount As String
        colProductName = TryCast(Me.gvMatch.Rows(0).FindControl("ddlFileColumnName"), DropDownList).SelectedValue
        colMonth = TryCast(Me.gvMatch.Rows(1).FindControl("ddlFileColumnName"), DropDownList).SelectedValue
        colForeCastCount = TryCast(Me.gvMatch.Rows(2).FindControl("ddlFileColumnName"), DropDownList).SelectedValue

        Dim dat As New clsData
        Dim sql As New StringBuilder
        sql.Length = 0
        sql.AppendFormat("truncate table ImportProduct ")
        dat.AccData(sql.ToString())

        For i As Integer = 0 To Me.ImportData.Rows.Count - 1
            sql.Length = 0
            sql.AppendFormat("insert into ImportProduct(ProductName,Month,ForeCastCount) ")
            sql.AppendFormat("select '{0}' ", Me.ImportData.Rows(i)(colProductName))
            sql.AppendFormat(",'{0}' ", Me.ImportData.Rows(i)(colMonth))
            sql.AppendFormat(",'{0}' ", Me.ImportData.Rows(i)(colForeCastCount))
            dat.AccData(sql.ToString())
        Next

        Me.divMatch.Visible = False
        Me.BindGV()
    End Sub

    Protected Sub BindGV()
        Dim dat As New clsData
        Dim sql As New StringBuilder
        sql.AppendFormat("select * from ImportProduct ")

        Me.gv.DataSource = dat.GetData(sql.ToString())
        Me.gv.DataBind()
    End Sub

    Protected Sub BindDBColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("ColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Dim row As Data.DataRow
        row = MyDt.NewRow()
        row("ColumnName") = "ProductName"
        MyDt.Rows.Add(row)

        row = MyDt.NewRow()
        row("ColumnName") = "Month"
        MyDt.Rows.Add(row)

        row = MyDt.NewRow()
        row("ColumnName") = "ForeCastCount"
        MyDt.Rows.Add(row)

        Me.DBColumnName = MyDt
    End Sub

    Protected Sub BindFileColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("FileColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Me.FileColumnName = MyDt
    End Sub

    Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf FileUpload1.PostedFile.ContentType.ToString() <> "application/vnd.ms-excel" AndAlso FileUpload1.PostedFile.ContentType.ToString() <> "application/octet-stream" Then
            MyObj.ShowMessage("匯入失敗!!請選擇副檔名(xls、csv)檔案!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        fileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        '檢查工作表名稱
        If ExcelImportCheck.LoadData(dt, newName, "Loreal", IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
            Return
        Else
            Me.ImportData = dt
        End If

        If Me.ImportData.Rows.Count > 0 Then
            Me.FileColumnName.Rows.Clear()
            For i As Integer = 0 To Me.ImportData.Columns.Count - 1
                Dim row As Data.DataRow
                row = Me.FileColumnName.NewRow()
                row("FileColumnName") = Me.ImportData.Columns(i).ColumnName
                Me.FileColumnName.Rows.Add(row)
            Next

            Me.divMatch.Visible = True
            Me.gvMatch.DataSource = Me.DBColumnName
            Me.gvMatch.DataBind()
        Else
            MyObj.ShowMessage("無資料可供匯入!!")
        End If

    End Sub

    Protected Sub gvMatch_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvMatch.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim ddlFileColumnName As DropDownList = TryCast(e.Row.FindControl("ddlFileColumnName"), DropDownList)
            ddlFileColumnName.DataTextField = "FileColumnName"
            ddlFileColumnName.DataValueField = "FileColumnName"
            ddlFileColumnName.DataSource = Me.FileColumnName
            ddlFileColumnName.DataBind()
            ddlFileColumnName.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
        End If
    End Sub

    Property DBColumnName() As Data.DataTable
        Get
            Return ViewState("DBColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("DBColumnName") = value
        End Set
    End Property

    Property ImportData() As Data.DataTable
        Get
            Return ViewState("ImportData")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("ImportData") = value
        End Set
    End Property

    Property FileColumnName() As Data.DataTable
        Get
            Return ViewState("FileColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("FileColumnName") = value
        End Set
    End Property

End Class
